package dbConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Set;
import java.util.Map.Entry;

import bean.*;
import util.CIUtil;
import util.ItemUtil;
import util.ReflectUtil;

public class DbConnection {

	private static DbConnection dbc;
	private static Connection conn;

	public static <E> ArrayList<E> getAllObject(E container) throws Exception {
		PreparedStatement pstat = getConnection().prepareStatement(
				"SELECT * FROM "
						+ ReflectUtil.getSimpleName(container).toLowerCase());
		pstat.setString(1, ReflectUtil.getSimpleName(container).toLowerCase());
		return getTableData(pstat, container);
	}

	public static Connection getConnection() {
		if (null == conn) {
			DbConnection.init();
		}
		return conn;
	}

	public static DbConnection getDbConnection() {
		if (null == dbc) {
			return new DbConnection();
		}
		return dbc;
	}

	public static FileInfo getFileInfoById(String fileInfoId) throws Exception {
		FileInfo fi = new FileInfo();
		fi.setId(fileInfoId);
		ArrayList<FileInfo> alfi = select(fi);
		if (alfi.size() == 1) {
			return alfi.get(0);
		} else {
			throw new Exception("Too many results.");
		}
	}

	public static <E> E getFirst(E e) throws Exception {
		ArrayList<E> ale = select(e);
		if (ale.size() > 0) {
			return ale.get(0);
		} else {
			throw new Exception("No results found.");
		}
	}

	public static <E> PreparedStatement getInsertSQLStatement(E e)
			throws Exception {
		boolean commaFlag = false;
		StringBuffer first = new StringBuffer();
		StringBuffer second = new StringBuffer();
		first.append("INSERT INTO "
				+ ReflectUtil.getSimpleName(e).toLowerCase() + " (");
		second.append(") VALUES(");
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(e);
		Set<Entry<String, Object>> entrySet = hm.entrySet();
		for (Entry<String, Object> entry : entrySet) {
			if (null != entry.getValue()
					&& (entry.getValue() instanceof String)) {

				if (commaFlag) {
					first.append(", ");
					second.append(", ");
				}
				first.append(entry.getKey());
				second.append("?");
				commaFlag = true;
			}
		}
		second.append(");");
		return getConnection()
				.prepareStatement(first.append(second).toString());
	}

	public static <E> PreparedStatement getSelectSqlStatement(E e,
			boolean isExact) throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM " + ReflectUtil.getSimpleName(e).toLowerCase());
		boolean whereFlag = true;
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(e);
		Set<Entry<String, Object>> entrySet = hm.entrySet();
		for (Entry<String, Object> entry : entrySet) {
			if (null != entry.getValue()) {
				if (whereFlag) {
					sb.append(" WHERE ");
					whereFlag = false;
				} else {
					sb.append(" AND ");
				}
				if (isExact) {
					sb.append(entry.getKey() + " = ?");
				} else {
					sb.append(entry.getKey() + " like ?");
				}
			}
		}
		sb.append(";");
		return getConnection().prepareStatement(sb.toString());
	}

	public static <E> E getSingle(E e) throws Exception {
		ArrayList<E> ale = select(e);
		if (ale.size() == 1) {
			return ale.get(0);
		} else {
			throw new Exception("Too many results");
		}
	}

	@SuppressWarnings("unchecked")
	public static <E> ArrayList<E> getTableData(PreparedStatement pstat,
			E container) throws Exception {
		ArrayList<E> ale = new ArrayList<E>();
		ResultSet rs;
		rs = pstat.executeQuery();
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(container);
		Set<Entry<String, Object>> entrySet = hm.entrySet();
		while (rs.next()) {
			for (Entry<String, Object> entry : entrySet) {
				try {
					String value = rs.getString(entry.getKey());
					hm.put(entry.getKey(), value);
				} catch (Exception e) {
					System.out.println("Column: " + entry.getKey()
							+ " not found!");
					continue;
				}
			}
			Class<?> clazz = Class.forName(container.getClass().getName());
			E e = (E) clazz.newInstance();
			ale.add(ReflectUtil.setObjectValues(hm, e));
		}
		rs.close();
		return ale;
	}

	public static Template getTemplateById(String templateId) throws Exception {

		Template temp = new Template();
		temp.setId(templateId);
		ArrayList<Template> altemp = select(temp);
		if (altemp.size() == 1) {
			return altemp.get(0);
		} else {
			throw new Exception("Too many results.");
		}
	}

	public static Template getTemplateByITEM(Item item) throws Exception {
		return getTemplateById(item.getTestCaseTemplateId());
	}

	public static Template getTemplateByTC(TestCase tc) throws Exception {
		return getTemplateByITEM(ItemUtil.getItemById(tc.getItemId()));
	}

	public static Template getTemplateByTS(TestSuite ts) throws Exception {
		return getTemplateById(CIUtil.getCiById(ts.getCommonInterfaceId())
				.getTestSuiteTemplate());
	}

	public static ArrayList<TestCase> getTestCaseByItem(Item item)
			throws Exception {
		TestCase tc = new TestCase();
		tc.setItemId(item.getId());
		return search(tc);
	}

	public static ArrayList<TestData> getTestDataByItem(String itemId)
			throws Exception {
		TestData td = new TestData();
		td.setItemId(itemId);
		return select(td);
	}

	public static TestSuite getTestSuiteByCiId(String commoninterfaceId)
			throws Exception {
		TestSuite ts = new TestSuite();
		ts.setCommonInterfaceId(commoninterfaceId);
		ArrayList<TestSuite> alts = select(ts);
		if (alts.size() == 1) {
			return alts.get(0);
		} else if (alts.size() > 1) {
			throw new Exception("Too many results.");
		} else if (alts.size() < 1) {
			throw new Exception("Target not found.");
		}
		return null;
	}

	public static TestSuite getTestSuiteById(String testSuiteId)
			throws Exception {
		TestSuite ts = new TestSuite();
		ts.setId(testSuiteId);
		ArrayList<TestSuite> alts = select(ts);
		if (alts.size() == 1) {
			return alts.get(0);
		} else {
			throw new Exception("Too many results.");
		}
	}

	public static <E> PreparedStatement getUpdateSQLStatement(E e)
			throws Exception {
		StringBuffer sb = new StringBuffer();
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(e);
		boolean commaFlag = false;
		sb.append("UPDATE " + ReflectUtil.getSimpleName(e).toLowerCase()
				+ " SET ");
		Set<Entry<String, Object>> entrySet = hm.entrySet();
		for (Entry<String, Object> entry : entrySet) {
			if (null != entry.getValue()
					&& (entry.getValue() instanceof String)) {

				if (commaFlag) {
					sb.append(", ");
				}
				sb.append(entry.getKey() + " = ?");
				commaFlag = true;
			}
		}
		sb.append(" WHERE id = '" + hm.get("id") + "';");
		return getConnection().prepareStatement(sb.toString());
	}

	public static void init() {

		// 驱动程序名
		String driver = "com.mysql.jdbc.Driver";

		// URL指向要访问的数据库名scutcs
		String url = "jdbc:mysql://127.0.0.1:3306/zit";

		// MySQL配置时的用户名
		String user = "root";

		// MySQL配置时的密码
		String password = "root";

		try {
			// 加载驱动程序
			Class.forName(driver);

			// 连续数据库
			conn = DriverManager.getConnection(url, user, password);
			if (!conn.isClosed())
				System.out.println("Succeeded connecting to the Database!");

		} catch (ClassNotFoundException e) {

			System.out.println("Sorry,can`t find the Driver!");
			e.printStackTrace();

		} catch (SQLException e) {

			e.printStackTrace();

		} catch (Exception e) {

			e.printStackTrace();

		}
	}

	public static void main(String[] args) {
		Item item = new Item();
		item.setName("r%");
		try {
			System.out.println(search(item).get(0).getName());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static <E> int save(E e) throws Exception {
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(e);
		PreparedStatement pstat;
		if (null == hm.get("id")) {
			pstat = getInsertSQLStatement(e);
		} else {
			pstat = getUpdateSQLStatement(e);
		}
		pstat = setStatementValue(e, pstat);
		return pstat.executeUpdate();
	}

	public static <E> ArrayList<E> search(E condition) throws Exception {
		PreparedStatement pstat = getSelectSqlStatement(condition, false);
		pstat = setStatementValue(condition, pstat);
		return getTableData(pstat, condition);
	}

	public static <E> ArrayList<E> select(E condition) throws Exception {

		PreparedStatement pstat = getSelectSqlStatement(condition, true);
		pstat = setStatementValue(condition, pstat);
		return getTableData(pstat, condition);
	}

	public static <E> PreparedStatement setStatementValue(E condition,
			PreparedStatement pstat) throws Exception {

		int pointer = 1;
		HashMap<String, Object> hm = ReflectUtil.getNameValueEntry(condition);
		Set<Entry<String, Object>> entrySet = hm.entrySet();
		for (Entry<String, Object> entry : entrySet) {
			if (null != entry.getValue()) {
				pstat.setString(pointer++, String.valueOf(entry.getValue()));
			}
		}
		return pstat;
	}
}
